<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Pelican Sightings Query Tool</title>
    <script src="https://cdn.jsdelivr.net/npm/@sqlite.org/sqlite-wasm@3.46.1-build4/sqlite-wasm/jswasm/sqlite3.mjs" type="module"></script>
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 0 auto;
            padding: 20px;
            line-height: 1.6;
        }
        textarea {
            width: 100%;
            height: 100px;
            margin-bottom: 10px;
        }
        button {
            background-color: #4CAF50;
            border: none;
            color: white;
            padding: 10px 20px;
            text-align: center;
            text-decoration: none;
            display: inline-block;
            font-size: 16px;
            margin: 4px 2px;
            cursor: pointer;
        }
        button:disabled {
            background-color: #cccccc;
            cursor: not-allowed;
        }
        table {
            border-collapse: collapse;
            width: 100%;
            margin-top: 20px;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #4CAF50;
            color: white;
        }
        tr:nth-child(even) {
            background-color: #f2f2f2;
        }
        #loading {
            display: none;
            color: #4CAF50;
            font-weight: bold;
        }
    </style>
</head>
<body>
    <h1>Pelican Sightings in Half Moon Bay</h1>
    <div id="loading">Loading SQLite and initializing database...</div>
    <textarea id="query" placeholder="Enter your SQL query here...">SELECT * FROM pelican_sightings;</textarea>
    <button id="executeButton" onclick="executeQuery()" disabled>Execute Query</button>
    <div id="result"></div>

    <script type="module">
        import sqlite3InitModule from 'https://cdn.jsdelivr.net/npm/@sqlite.org/sqlite-wasm@3.46.1-build4/sqlite-wasm/jswasm/sqlite3.mjs';

        const loadingElement = document.getElementById('loading');
        const executeButton = document.getElementById('executeButton');

        const initializeSQLite = async () => {
            loadingElement.style.display = 'block';
            try {
                const sqlite3 = await sqlite3InitModule({
                    print: console.log,
                    printErr: console.error,
                });
                console.log('Running SQLite version', sqlite3.version.libVersion);
                window.db = new sqlite3.oo1.DB();
                
                window.db.exec(`
                    CREATE TABLE pelican_sightings (
                        id INTEGER PRIMARY KEY,
                        date TEXT,
                        location TEXT,
                        species TEXT,
                        count INTEGER
                    );
                    INSERT INTO pelican_sightings (date, location, species, count) VALUES
                    ('2024-10-01', 'Miramar Beach', 'Brown Pelican', 12),
                    ('2024-10-02', 'Pillar Point Harbor', 'California Brown Pelican', 8),
                    ('2024-10-03', 'Half Moon Bay State Beach', 'American White Pelican', 3),
                    ('2024-10-04', 'Dunes Beach', 'Brown Pelican', 15),
                    ('2024-10-05', 'Poplar Beach', 'California Brown Pelican', 10);
                `);
                console.log('Database initialized with sample data');
                executeButton.disabled = false;
            } catch (err) {
                console.error('Initialization error:', err.message);
                document.getElementById('result').innerHTML = `<p>Error initializing database: ${err.message}</p>`;
            } finally {
                loadingElement.style.display = 'none';
            }
        };

        initializeSQLite();

        window.executeQuery = () => {
            const query = document.getElementById('query').value;
            try {
                const results = window.db.selectObjects(query);
                displayResults(results);
            } catch (err) {
                document.getElementById('result').innerHTML = `<p>Error: ${err.message}</p>`;
            }
        };

        function displayResults(results) {
            if (results.length === 0) {
                document.getElementById('result').innerHTML = '<p>No results.</p>';
                return;
            }
            let columns = Object.keys(results[0]);
            let tableHtml = '<table><tr>';
            columns.forEach(column => {
                tableHtml += `<th>${column}</th>`;
            });
            tableHtml += '</tr>';

            results.forEach(row => {
                tableHtml += '<tr>';

                columns.forEach(column => {
                    tableHtml += `<td>${row[column]}</td>`;
                });
                tableHtml += '</tr>';
            });
            tableHtml += '</table>';

            document.getElementById('result').innerHTML = tableHtml;
        }
    </script>
</body>
</html>
